As part of the distribution of information to area chairs in NIPS 2014 we used Google docs to provide summary spreadsheets. Review information is processed in pandas so this rough interface is designed to allow uploading and downloading of information from google spreadsheets as pandas data frames.
In [ ]:
import pods.google
import pandas as pd
Next you'll need to make use of two gmail user IDs for this example.
In [ ]:
user1 = 'user1@gmail.com'
user2 = 'user2@sheffield.ac.uk'
First let's create a simple data frame for placing in a spreadsheet.
In [ ]:
d = {'one' : pd.Series([1., 2., 3.], index=['a', 'b', 'c']),
'two' : pd.Series([1., 2., 3., 4.], index=['a', 'b', 'c', 'd'])}
df = pd.DataFrame(d)
In [ ]:
df
In [ ]:
sheet = pods.google.sheet()
The spreadsheet is uniquely defined by its resource id, which can be recovered from the spreadsheet as follows:
In [ ]:
print(sheet.resource._id)
The spreadsheet can be shared (by default with 'edit' access) to other gmail users.
In [ ]:
sheet.share(user1)
In [ ]:
sheet.share_delete(user1)
To share with 'view' access you pass an extra argument, share_type='reader'. You can also send a notification (by default there is no mail sent).
In [ ]:
sheet.share(user1)
sheet.share_modify(user1,share_type='reader',send_notifications=True)
The spreadsheet's title can also be renamed.
In [ ]:
sheet.set_title('New Title')
And the new title exhibited.
In [ ]:
sheet.get_title()
Or it can be deleted.
In [ ]:
sheet.resource.delete()
In [ ]:
import pods
resource = pods.google.resource(id='1nnUOpV30Jo9ISYPtkqSWbTLzymo5EBxA9056gKdLh1Q')
sheet = pods.google.sheet(resource=resource)
In [ ]:
df2 = sheet.read()
In [ ]:
df2
For this google doc, now it's created we've published it to the web. Quoting from the developer's site
"Publishing a spreadsheet to the web can only be done from the Google sheet's user interface. To start publishing a spreadsheet to the web, select File > Publish to the web from the sheet's user interface, and then click the Start Publishing button."
Once the spreadsheet is published you can have people viewing it publicly as follows.
In [ ]:
sheet.share([user1, user2])
Or you can modify the access rights they have to the document.
In [ ]:
sheet.share_modify(user2, 'writer')
Or you can remove them from the sharing list completely.
In [ ]:
sheet.share_delete(user1)
To see who the document is shared with you can use the share_list() method.
In [ ]:
sheet.share_list()
In [ ]:
from IPython.display import display
display(sheet)
Or if the spreadsheet is published you can set sheet.published = True and get a representation of the spreadsheet embedded in the notebook.
In [ ]:
df2 = sheet.read()
df2
We can now update the spreadsheet, by modifying the data frame, and then requesting an update. The update command looks only for entries that have changed.
In [ ]:
df2['one']['a'] = 2.3
sheet.update(df2)
sheet
You should see the spreadsheet has changed. You can also delete rows.
In [ ]:
sheet.update(df2.drop('a'))
display(sheet)
Or add them in again. Here we update back to the original data frame (df). Note when you look at the google doc that the row is added at the end of the spreadsheet. This is so that any sorting of other rows in the spreadsheet is preserved.
In [ ]:
sheet.update(df)
Let's open the sheet again with a new interface.
In [ ]:
sheet2 = pods.google.sheet(resource=sheet.resource)
df4 = sheet2.read()
print(df4)
Note that because we deleted the row indexed by 'b' and then updated the spreadsheet, the row of b was added back at the end of the spreadsheet.
In [ ]:
sheet2.update(df4.drop('c'))
In [ ]:
display(sheet2)